﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using StockFinder.Model;
using System.Data.SqlClient;

namespace StockFinder.DataAccess
{
    public class IBDDataAccess : DataAccessBase
    {
        private const string INSERT_IBDRATING = "dbo.InsertIBDRating";
        private const string GET_IBDRATINGS_BY_DATE = "dbo.GetAllIBDRatingByDate";
        private const string DELETE_ALL_IBD_RATINGS = "dbo.DeleteAllIBDRatings";

        public void DeleteAllIBDRatings()
        {
            SqlConnection connection = CreateOpenConnection();

            using (SqlCommand command = new SqlCommand(DELETE_ALL_IBD_RATINGS, connection) { CommandType = System.Data.CommandType.StoredProcedure })
            {
                command.ExecuteNonQuery();
            }
        }

        public List<IBDRatings> GetAllIBDRatingsByDate(DateTime datetime)
        {
            List<IBDRatings> ratings = null;

            using (SqlConnection connection = new SqlConnection(ApplicationConfiguation.DatabaseConnectionString))
            {
                connection.Open();
                using (SqlCommand command = new SqlCommand(GET_IBDRATINGS_BY_DATE, connection) { CommandType = System.Data.CommandType.StoredProcedure })
                {
                    command.Parameters.AddWithValue("@Date", datetime.Date);

                    using (SqlDataReader dataReader = command.ExecuteReader())
                    {
                        ratings = new List<IBDRatings>();

                        if (dataReader.HasRows)
                        {
                            while (dataReader.Read())
                            {
                                ratings.Add(new IBDRatings()
                                {
                                    Symbol = Convert.ToString(dataReader["Symbol"]),
                                    GroupRSRating = Convert.ToString(dataReader["GroupRS"]),
                                    AccDist = Convert.ToString(dataReader["AccDist"]),
                                    Date = Convert.ToDateTime(dataReader["Date"]),
                                    EPS = Convert.ToInt32(dataReader["EPS"]),
                                    SMR = Convert.ToString(dataReader["SMR"]),
                                    IBDComposite = Convert.ToInt32(dataReader["Composite"]),
                                    RelativeStrength = Convert.ToInt32(dataReader["RS"])
                                });
                            }
                        }
                    }
                }
            }

            return ratings;
        }

        public static void InsertIBDRating(IBDRatings rating)
        {
            using (SqlConnection connection = new SqlConnection(ApplicationConfiguation.DatabaseConnectionString))
            {
                connection.Open();

                using (SqlCommand command = new SqlCommand(INSERT_IBDRATING, connection) { CommandType = System.Data.CommandType.StoredProcedure })
                {
                    command.Parameters.AddWithValue("@Symbol", rating.Symbol);
                    command.Parameters.AddWithValue("@Date", rating.Date);
                    command.Parameters.AddWithValue("@Composite", rating.IBDComposite);
                    command.Parameters.AddWithValue("@EPS", rating.EPS);
                    command.Parameters.AddWithValue("@RS", rating.RelativeStrength);
                    command.Parameters.AddWithValue("@SMR", rating.SMR);
                    command.Parameters.AddWithValue("@AccDist", rating.AccDist);
                    command.Parameters.AddWithValue("@GroupRS", rating.GroupRSRating);

                    command.Parameters.AddWithValue("@IsETF", rating.IsETF);
                    command.Parameters.AddWithValue("@IsETFOrClosedFund", rating.IsETFOrClosedEndFund);                    

                    command.Parameters.Add(new SqlParameter() { ParameterName = "RETURN_VALUE", Direction = System.Data.ParameterDirection.ReturnValue });

                    command.ExecuteNonQuery();

                    //int result = (int)command.Parameters["RETURN_VALUE"].Value;

                    //symbol.SymbolId = result;
                }
            }
        }
    }
}
